Demonstration on PostgreSQL wiki:
https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)
Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017
Getting all members with their recommendee
(external)
select mem."firstname" || ' ' || mem."surname",
memref."firstname" || ' ' || memref."surname"
from "members" as mem
left outer join "members" as memref on mem."recommendedby" = memref."memid"
select mem."firstname" || ' ' || mem."surname",
( select memref."firstname" || ' ' || memref."surname" from members as memref where mem."recommendedby" = memref."memid" )
from members as mem
(Inside a Select clause)
<query specification> ::= SELECT [ <set
quantifier> ] <select
list> <table expression>
<select
list> ::=
<asterisk>
| <select sublist> [ {
<comma> <select sublist> }... ]
<select sublist> ::=
<derived column>
| <qualified asterisk>
<derived
column> ::=
<value expression> [
<as clause> ]
<value expression> ::=
<numeric value expression>
| <string value expression>
| <datetime value expression>
| <interval value expression>
| <boolean value expression>
| <user-defined type value expression>
| <row value expression>
| <reference value
expression>
| <collection value expression>
<reference value expression> ::=
<value expression
primary>
<value expression primary> ::=
<parenthesized value expression> | <nonparenthesized value expression
primary>
<nonparenthesized value expression primary>
::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <scalar subquery>
| <case expression>
| <cast specification>
| <subtype treatment>
| <attribute or method reference>
| <reference resolution>
| <collection value constructor>
| <routine invocation>
| <field reference>
| <element reference>
| <method invocation>
| <static method invocation>
| <new specification>
<scalar subquery> ::= <subquery>
<subquery> ::=
<left paren> <query
expression> <right paren>
The degree
of a <scalar subquery> shall be 1 (one)
(Inside a Select clause)
select
( select
"columnName"
from tableName
where ...
)
with memref as ( select "firstname" || ' ' || "surname" as refname from members )
select mem."firstname" || ' ' || mem."surname",
memref.refname
from members as mem
left outer join memref on mem."recommendedby" = memref."memid"
with
Clause<query expression> ::=
[ <with clause> ]
<query expression body>
[ <order by clause> ]
[ <result offset clause> ]
[ <fetch first clause> ]
<with
clause> ::=
WITH [ RECURSIVE ] <with
list>
<with list> ::=
<with list element> [ {
<comma> <with list element> }...
]
<with
list element> ::=
<query name> [ <left paren> <with column
list> <right paren> ]
AS <table subquery> [
<search or cycle clause> ]
with "mySubqueryName"
(
column1,
column2,
...
)
as
(
whatever subquery
)
select
from "mySubqueryName"
...
select
mem."firstname"
|| ' ' ||
mem."surname",
memref."firstname"
|| ' ' ||
memref."surname"
from
"members" as mem
left outer join
"members" as memref
on mem."recommendedby" = memref."memid"
select mem."firstname", mem."surname",
( select memref."firstname", memref."surname", from members as memref where mem."recommendedby" = memref."memid" )
from members as mem
select mem."firstname", mem."surname",
memref."firstname", memref."surname"
from members as mem
left join lateral (select "firstname", "surname" from members as mentors where mentors.memid = mem.recommendedby) as memref on true
<query expression> ::=
[ <with clause> ]
<query expression
body>
<query
expression body> ::=
<non-join query expression>
| <joined table>
<joined table> ::= <cross join>
| <qualified join>
| <natural join>
| <union join>
<qualified join>
::=
<table reference> [ <join type> ] JOIN
<table reference>
<join specification>
<table reference> ::=
<table primary> |
<joined table>
<table primary> ::=
<table or query name> [ [ AS ] <correlation
name>
[ <left paren> <derived column list>
<right paren> ] ]
| <derived table> [ AS ] <correlation
name>
[ <left paren> <derived column list>
<right paren> ]
| <lateral derived
table> [ AS ] <correlation name>
[ <left paren> <derived column list>
<right paren> ]
| <collection derived table> [ AS ] <correlation
name>
[ <left paren> <derived column list>
<right paren> ]
| <only spec>
[ [ AS ] <correlation name>
[ <left paren> <derived column list>
<right paren> ] ]
| <left paren> <joined table> <right
paren>
<lateral derived table> ::=
LATERAL <left paren> <query
expression> <right paren>
<join specification> ::=
<join condition>
| <named columns join>
<join
condition> ::= ON <search
condition>
<search
condition> ::=
<boolean value
expression>
Getting the list of members who's name is David, Tim or Darren
select "firstname", "surname" from members
where "firstname" = 'David' or "firstname" = 'Tim' or "firstname" = 'Darren'
select "firstname", "surname" from members
where "firstname" in ('David', 'Tim', 'Darren')
insert into ("column1",...) values (...);
values ('Hello world');
select "firstname", "surname" from members
inner join values (('David'),('Tim'),('Darren')) as people(firstname) on members."firstname" = people."firstname"
<query expression> ::=
<non-join query
expression>
| <joined table>
<non-join query expression>
::=
<non-join query
term>
| <query expression> UNION [ ALL ] [
<corresponding spec> ] <query term>
| <query expression> EXCEPT [ ALL ] [
<corresponding spec> ] <query term>
<non-join query term> ::=
<non-join query
primary>
| <query term> INTERSECT [ ALL ] [
<corresponding spec> ] <query
primary>
<non-join query primary>
::=
<simple table>
| <left paren> <non-join query expression>
<right paren>
<simple table> ::=
<query specification>
| <table value
constructor> | <explicit
table>
<table value constructor>
::=
VALUES <table value constructor
list>
Getting the list of members who's never booked an equipment
select surname, firstname from members
where "memid" not in ( select memid from bookings )
select surname, firstname from members
where not exists ( select 1 from bookings where bookings.memid = members.memid )
select surname, firstname from members
left outer join "bookings" on "members"."memid" = "bookings"."memid" where "bookings".memid is null
For the year 2012, getting for each equipment the duration of use per month, then their total per year and then the total for all equipments for the year
select "facid", extract(month from starttime), sum(slots) from "bookings" where extract(year from starttime) = 2012 group by factid, extract(month from starttime)
union select "facid", null, sum(slots) from "bookings" where extract(year from starttime) = 2012 group by factid
union select null, null, sum(slots) from "bookings" where extract(year from starttime) = 2012
select facid, extract(month from starttime), sum(slots) as slots from cd.bookings where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime")) order by "facid", extract(month from "starttime")
<group by clause> ::=
GROUP BY <grouping
specification>
<grouping specification>
::=
<grouping column reference>
| <rollup list>
| <cube list>
| <grouping sets list>
| <grand total>
| <concatenated grouping>
<rollup list> ::=
ROLLUP <left paren>
<grouping column reference list> <right
paren>
For each member, display their first and last names allong with the total number of members in the club
select (
select count(*) from members
) as count, firstname, surname from members order by joindate
select count(*)
over(),
"firstname", "surname" from members order by joindate
<window function> ::= <window function
type> OVER <window name or
specification>
<window name or
specification> ::=
<window name>
| <in-line window
specification>
<in-line window
specification> ::= <window
specification>
<window specification> ::=
<left paren> <window
specification details> <right
paren>
<window specification
details> ::=
[ <existing window name> ]
[ <window partition clause> ]
[ <window order clause> ]
[ <window frame clause> ]
For the member who's id is 27, find the chained list of recommenders
with recursive recommenders(recommender) as (
select recommendedby from cd.members where memid = 27
union all select mems.recommendedby from recommenders recs inner join cd.members mems on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname from recommenders recs inner join cd.members mems on recs.recommender = mems.memid order by memid desc